package com.ssnoodles.weathersimple.db;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;

/**
 * Created by ssnoodles on 2016/10/17.
 */

public class DbHelper extends SQLiteOpenHelper{

    private static final String TAG = DbHelper.class.getSimpleName();

    public static final String DB_NAME = "weather.db";

    private Context mContext;

    public DbHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
        mContext = context;
    }

    /**
     * Province表建表语句
     */
    public static final String CREATE_PROVINCE = "create table Province ("
            + "id integer primary key autoincrement, "
            + "province_name text, "
            + "province_code text)";
    /**
     * City表建表语句
     */
    public static final String CREATE_CITY = "create table City ("
            + "id integer primary key autoincrement, "
            + "city_name text, "
            + "city_code text, "
            + "province_code text)";
    /**
     * County表建表语句
     */
    public static final String CREATE_COUNTY = "create table County ("
            + "id integer primary key autoincrement, "
            + "county_name text, "
            + "county_code text, "
            + "city_code text)";

    @Override
    public void onCreate(SQLiteDatabase db) {

        if (tabIsExist(db, "Province")) return;
        if (tabIsExist(db, "City")) return;
        if (tabIsExist(db, "County")) return;

        // 创建表
        db.execSQL(CREATE_PROVINCE); // 创建Province表
        db.execSQL(CREATE_CITY); // 创建City表
        db.execSQL(CREATE_COUNTY); // 创建County表

        // 初始化数据
        executeAssetsSQL(db, "Province.sql");
        executeAssetsSQL(db, "City.sql");
        executeAssetsSQL(db, "County.sql");

        Log.d(TAG, "init city data success!!!");
    }

    /**
     * 读取数据库文件（.sql），并执行sql语句
     * */
    private void executeAssetsSQL(SQLiteDatabase db, String sqlFileName) {
        BufferedReader in = null;
        try {
            in = new BufferedReader(new InputStreamReader(mContext.getAssets().open(sqlFileName)));

            String line;
            while ((line = in.readLine()) != null) {
                db.execSQL(line);
            }
        } catch (IOException e) {
            Log.e(TAG, e.toString());
        } finally {
            try {
                if (in != null)
                    in.close();
            } catch (IOException e) {
                Log.e(TAG, e.toString());
            }
        }
    }

    /**
     * 判断是否存在某一张表
     * @param tabName
     * @param db
     * @return
     */
    private boolean tabIsExist(SQLiteDatabase db, String tabName) {
        boolean result = false;
        if (tabName == null) {
            return false;
        }
        try {
            String sql = "select count(*) from sqlite_master where type ='table' and name ='" + tabName.trim() + "'";
            Cursor cursor = db.rawQuery(sql, null);
            while (cursor.moveToNext()) {
                int count = cursor.getInt(0);
                if (count > 0) {
                    result = true;
                    break;
                }
            }
            cursor.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
        Log.d(TAG, tabName + " is " + result);
        return result;
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }


}
